package com.yy;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Before;
import org.junit.Test;

import com.alibaba.fastjson.JSON;

import CustomClassFile.CronJob.DateUtil;
import CustomClassFile.CronJob.Pbzxx;
import CustomClassFile.CronJob.ZswsHRaShift;
import CustomClassFile.yueli.KaoqinDk;
import CustomClassFile.yueli.Yueli;
import net.sf.json.JSONObject;
import weaver.conn.RecordSet;
import weaver.general.GCONST;
import weaver.general.StaticObj;

public class Paibanshuju {
	public static final Log logger = LogFactory.getLog(Yueli.class);

	@Before
	public void before() {
		GCONST.setRootPath("WebContent/");
		GCONST.setServerName("ecology");
	}

	//@Test // test
	public void test() {
		String id = "4";
		String date = "2018-10-23";
		// Map<String, Integer> calendarWork = getcalendarDays(date);
		Map<String, Object> mapinfo = Yueli.getinfo(id, date);
		String mapjson = JSON.toJSONString(mapinfo);
		logger.info(mapjson);
		Yueli.getMonthPb(id, date);
	}

	//@Test // test测试插入考勤数据sql
	public void test2() {
		String curruserid = "4";
		String kqrq = DateUtil.getCurrentDateTime("yyyy-MM-dd");
		String kqsj = DateUtil.getCurrentDateTime("HH:mm");
		String kqdd = "test";
		String kqdjwd = "22,33";
		String kqdkjd = "22";
		String kqdkwd = "33";
		String curraddress = "test";
		String formmodeid = null;
		String modedatacreater = null;
		String modedatacreatertype = null;
		String modedatacreatedate = null;
		String modedatacreatetime = null;
		String kqlx = "0";
		String pbsj = "12:00";
		String sbxb = "1";
		String kqdbh = "test";
		String bcbh = "Nor";
		String isflow = "0";
		String dkcard = "card2";
		String dkcardstatus = "0";
		String flowdataid = null;
		boolean flag = KaoqinDk.insertDaKa(curruserid, kqrq, kqsj, kqdd, kqdjwd, kqdkjd, kqdkwd, curraddress,
				formmodeid, modedatacreater, modedatacreatertype, modedatacreatedate, modedatacreatetime, kqlx, pbsj,
				sbxb, kqdbh, bcbh, isflow, dkcard, dkcardstatus, flowdataid);
		System.out.println(flag);

	}

	//同步排班
	//@Test
	public void test4() {

		List<ZswsHRaShift> lists = new ArrayList<ZswsHRaShift>();
		JSONObject jsonObject = new JSONObject();
		String returnCode = "";
		String returnMessage = "";
		int add = 0;
		int update = 0;
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		int datalength = 0;
		try {

			String sql = "select eid,name,shift,convert(varchar(19),term,20) as term,title,xtype from zswsHRaShift where convert(date,term,20) >convert(date,getdate(),20) order by eid ,term";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				String eid = resultSet.getString("eid");
				String name = resultSet.getString("name");
				String shift = resultSet.getString("shift");
				String term = resultSet.getString("term");
				String title = resultSet.getString("title");
				String xtype = resultSet.getString("xtype");
				ZswsHRaShift zs = new ZswsHRaShift(eid, name, shift, term, title, xtype);
				lists.add(zs);
				datalength++;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		List<List> insertlist = new ArrayList<List>();
		List<List> updatelist = new ArrayList<List>();
	/*	for (ZswsHRaShift zsws : lists) {
			RecordSet rs0 = new RecordSet();
			String sql0 = "select * from uf_rypbxx_test where eid=? and term=?";
			boolean flag = rs0.executeQuery(sql0, zsws.getEid(), zsws.getTerm());
			if (flag && rs0.next()) {
				List<String> updateparam = new ArrayList<String>();
				updateparam.add(zsws.getEid());
				updateparam.add(zsws.getName());
				updateparam.add(zsws.getShift());
				updateparam.add(zsws.getTerm());
				updateparam.add(zsws.getTitle());
				updateparam.add(zsws.getXtype());
				updateparam.add(rs0.getString("id"));

				updatelist.add(updateparam);
				update++;
			} else {
				List<String> insertparam = new ArrayList<String>();
				insertparam.add(zsws.getEid());
				insertparam.add(zsws.getName());
				insertparam.add(zsws.getShift());
				insertparam.add(zsws.getTerm());
				insertparam.add(zsws.getTitle());
				insertparam.add(zsws.getXtype());
				insertlist.add(insertparam);
				add++;
			}
		}*/
		//先删除今天之后的所有排班
		RecordSet rs0 = new RecordSet();
		String sql0="delete from uf_rypbxx where convert(date,term,20) >convert(date,getdate(),20)";
		rs0.execute(sql0);
		//让后插入今天之后的排班
		for (ZswsHRaShift zsws : lists) {
			List<String> insertparam = new ArrayList<String>();
			insertparam.add(zsws.getEid());
			insertparam.add(zsws.getName());
			insertparam.add(zsws.getShift());
			insertparam.add(zsws.getTerm());
			insertparam.add(zsws.getTitle());
			insertparam.add(zsws.getXtype());
			insertlist.add(insertparam);
			add++;
			}
		RecordSet rs1 = new RecordSet();
		String insertbatch = "insert into uf_rypbxx(eid,name,shift,term,title,xtype) values(?,?,?,?,?,?)";
		rs1.executeBatchSql(insertbatch, insertlist);

//		RecordSet rs2 = new RecordSet();
//		String updatebatch = "update uf_rypbxx_test set eid=?,name=?,shift=?,term=?,title=?,xtype=? where id=?";
//		rs2.executeBatchSql(updatebatch, updatelist);

		jsonObject.put("code", returnCode);
		jsonObject.put("message", returnMessage);
		jsonObject.put("add", add);
		jsonObject.put("update", update);
		jsonObject.put("total", datalength);
		System.out.println(jsonObject.toString());
	}
	
	//同步排班（同步所有数据,覆盖同步）；
	//@Test
	public void test5() {

		List<ZswsHRaShift> lists = new ArrayList<ZswsHRaShift>();
		JSONObject jsonObject = new JSONObject();
		String returnCode = "";
		String returnMessage = "";
		int add = 0;
		int update = 0;
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		int datalength = 0;
		//查询视图所有数据
		try {

			String sql = "select eid,name,shift,convert(varchar(19),term,20) as term,title,xtype from zswsHRaShift order by eid ,term";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {

				String eid = resultSet.getString("eid");
				String name = resultSet.getString("name");
				String shift = resultSet.getString("shift");
				String term = resultSet.getString("term");
				String title = resultSet.getString("title");
				String xtype = resultSet.getString("xtype");
				ZswsHRaShift zs = new ZswsHRaShift(eid, name, shift, term, title, xtype);
				lists.add(zs);
				datalength++;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		List<List> insertlist = new ArrayList<List>();
		//清空表数据
		RecordSet rs0 = new RecordSet();
		String sql0="truncate table uf_rypbxx";
		rs0.execute(sql0);
		//让后插入今天之后的排班
		for (ZswsHRaShift zsws : lists) {
			List<String> insertparam = new ArrayList<String>();
			insertparam.add(zsws.getEid());
			insertparam.add(zsws.getName());
			insertparam.add(zsws.getShift());
			insertparam.add(zsws.getTerm());
			insertparam.add(zsws.getTitle());
			insertparam.add(zsws.getXtype());
			insertlist.add(insertparam);
			add++;
			}
		RecordSet rs1 = new RecordSet();
		String insertbatch = "insert into uf_rypbxx(eid,name,shift,term,title,xtype) values(?,?,?,?,?,?)";
		rs1.executeBatchSql(insertbatch, insertlist);
		jsonObject.put("code", returnCode);
		jsonObject.put("message", returnMessage);
		jsonObject.put("add", add);
		jsonObject.put("update", update);
		jsonObject.put("total", datalength);
		System.out.println(jsonObject.toString());
	}
	
	//同步排班组数据
	//@Test
	public void test6() {

		List<List> insertlist = new ArrayList<List>();
		JSONObject jsonObject = new JSONObject();
		weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
				.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
		java.sql.Connection conn = ds.getConnection();
		int datalength = 0;
		try {

			String sql = "select * from zswsHRaShift_type";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				List<String> insertparam=new ArrayList<String>();
				insertparam.add(rs.getString("card1"));
				insertparam.add(rs.getString("card2"));
				insertparam.add(rs.getString("card3"));
				insertparam.add(rs.getString("card4"));
				insertparam.add(rs.getString("CardTimes"));
				insertparam.add(rs.getString("endT1"));
				insertparam.add(rs.getString("endT2"));
				insertparam.add(rs.getString("endT3"));
				insertparam.add(rs.getString("endT4"));
				insertparam.add(rs.getString("IsDisabled"));
				insertparam.add(rs.getString("scan1"));
				insertparam.add(rs.getString("scan2"));
				insertparam.add(rs.getString("scan3"));
				insertparam.add(rs.getString("scan4"));
				insertparam.add(rs.getString("Shift"));
				insertparam.add(rs.getString("Title"));
				insertparam.add(rs.getString("xHours"));
				datalength++;
				insertlist.add(insertparam);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}


		RecordSet rs0 = new RecordSet();
		String sql0="delete from uf_pbzxx";
		rs0.execute(sql0);
		RecordSet rs1 = new RecordSet();
		String insertbatch = "insert into uf_pbzxx(card1,card2,card3,card4,CardTimes,endT1,endT2,endT3,endT4,IsDisabled,scan1,scan2,scan3,scan4,Shift,Title,xHours) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		rs1.executeBatchSql(insertbatch, insertlist);

		jsonObject.put("total", datalength);
		System.out.println(jsonObject.toString());
	}	
	
	@Test
	public  void test9() {
		
		String sql="select t.* from hrmresource  h inner join uf_rypbxx z on h.outkey=z.eid and h.id=? and CONVERT(date,z.term,20)=? inner join uf_pbzxx t on z.shift=t.shift and (t.isdisabled is null or t.isdisabled='1')";
		sql="";
		RecordSet rs=new RecordSet();
		boolean flag=rs.executeQuery(sql,"236",DateUtil.getCurrentDateTime("yyyy-MM-dd"));
		if (flag) {
			while(rs.next()) {
				System.out.println(rs.getString("isDisabled")+"---");
			}
		}
		
	}
}
